set more off
 

clear

import excel "191204_1022_CTR.xlsx", sheet("191204_1022 - CTR") firstrow
rename _1022 session
replace treatment=1
save "191204_1022_CTR.dta", replace
clear
 
import excel "191204_1206_GQ.xlsx", sheet("191204_1206 - GQ") firstrow
rename _1206 session
replace treatment=2
save "191204_1206_GQ.dta", replace
clear

import excel "191204_1358_MIX.xlsx", sheet("191204_1358 - MIX") firstrow
rename _1358 session
replace treatment=3
save "191204_1358_MIX.dta", replace
clear

import excel "191205_1007_GQ.xlsx", sheet("191205_1007 - GQ") firstrow
rename _1007 session
replace treatment=2
save "191205_1007_GQ.dta", replace
clear

import excel "191205_1206_MIX.xlsx", sheet("191205_1206 - MIX") firstrow
rename _1206 session
replace treatment=3
save "191205_1206_MIX.dta", replace
clear

import excel "191205_1352_CTR.xlsx", sheet("191205_1352 - CTR") firstrow
rename _1352 session
replace treatment=1
save "191205_1352_CTR.dta", replace
clear

import excel "191209_1220_GQ.xlsx", sheet("191209_1220 - GQ") firstrow
rename _1220 session
replace treatment=2
save "191209_1220_GQ.dta", replace
clear

import excel "191209_1355_MIX.xlsx", sheet("191209_1355 - MIX") firstrow
rename _1355 session
replace treatment=3
save "191209_1355_MIX.dta", replace
clear

import excel "191213_1218_CTR.xlsx", sheet("191213_1218 - CTR") firstrow
rename _1218 session
replace treatment=1
save "191213_1218_CTR.dta", replace
clear

import excel "191217_1218_CTR.xlsx", sheet("191217_1218 - CTR") firstrow
rename _1218 session
replace treatment=1
save "191217_1218_CTR.dta", replace
clear

import excel "200115_1159_MIX.xlsx", sheet("200115_1159 - MIX") firstrow
rename _1159 session
replace treatment=3
save "200115_1159_MIX.dta", replace
clear

import excel "200115_1404_GQ.xlsx", sheet("200115_1404 - GQ") firstrow
rename _1404 session
replace treatment=2
save "200115_1404_GQ.dta", replace
clear

import excel "200121_1215_MIX.xlsx",sheet("200121_1215 - MIX") firstrow
rename _1215 session
replace treatment=3
save "200121_1215_MIX.dta", replace


//..............APPEND........................//
use "191204_1022_CTR.dta"
append using 191204_1206_GQ.dta, force
append using 191204_1358_MIX.dta, force
append using 191205_1007_GQ.dta, force
append using 191205_1206_MIX.dta, force
append using 191205_1352_CTR.dta, force
append using 191209_1220_GQ.dta, force
append using 191209_1355_MIX.dta, force
append using 191213_1218_CTR.dta, force
append using 191217_1218_CTR.dta, force
append using 200115_1159_MIX.dta, force
append using 200115_1404_GQ.dta, force
append using 200121_1215_MIX.dta, force

sort session Group
gen uGroup=.
forval i=1/4{
replace uGroup= `i' if (Group ==`i' & session == "191204_1022")
}
forval i=1/4{
replace uGroup= `i'+4 if (Group ==`i' & session == "191204_1206")
}
forval i=1/4{
replace uGroup= `i'+8 if (Group ==`i' & session == "191204_1358")
}
forval i=1/4{
replace uGroup= `i'+12 if (Group ==`i' & session == "191205_1007")
}
forval i=1/3{
replace uGroup= `i'+16 if (Group ==`i' & session == "191205_1206")
}
forval i=1/3{
replace uGroup= `i'+19 if (Group ==`i' & session == "191205_1352")
}
forval i=1/4{
replace uGroup= `i'+22 if (Group ==`i' & session == "191209_1220")
}
forval i=1/4{
replace uGroup= `i'+26 if (Group ==`i' & session == "191209_1355")
}
forval i=1/2{
replace uGroup= `i'+30 if (Group ==`i' & session == "191213_1218")
}
forval i=1/2{
replace uGroup= `i'+32 if (Group ==`i' & session == "191217_1218")
}
forval i=1/2{
replace uGroup= `i'+34 if (Group ==`i' & session == "200115_1159")
}
forval i=1/2{
replace uGroup= `i'+36 if (Group ==`i' & session == "200115_1404")
}
forval i=1/2{
replace uGroup= `i'+38 if (Group ==`i' & session == "200121_1215")
}
***
gen id=_n
***create ranktT3 (compare correctT3 with correctT2 of other five group members) for subjects who did not choose tournament in Stage 3
***this was not calculate in Ztree, thus rankT3 of obs with choiceT3=0 is 0
gen rankT3dummy =.
forval i=1/40{
forval j= 1/6{
gen correctT3_`i'_`j' = correctT2 if uGroup== `i'
replace correctT3_`i'_`j' = correctT3 if (uGroup== `i' & Number== `j' )
egen rankT3_`i'_`j' = rank(-correctT3_`i'_`j'), track
replace rankT3dummy = rankT3_`i'_`j' if (uGroup== `i' & Number== `j' )
}
}
***
forval i=1/40{
forval j= 1/6{
drop correctT3_`i'_`j' 
drop rankT3_`i'_`j' 
}
}
***
recode choiceT3 (2=1) (1=0)
recode choiceT4 (2=1) (1=0)
recode risk (0=1) (1=2) (2=3) (3=4) (4=5) (5=6) (6=7) (7=8) (8=9) (9=10) (10=11) (11=12)

*recode fairness_CTR (0=1) (1=2) (2=3) (3=4) (4=5) (5=6) (6=7)
*recode fairness_gender (0=1) (1=2) (2=3) (3=4) (4=5) (5=6) (6=7)
*recode fairness_mix (0=1) (1=2) (2=3) (3=4) (4=5) (5=6) (6=7)
*recode fairness (0=1) (1=2) (2=3) (3=4) (4=5) (5=6) (6=7)

replace rankT3 = rankT3dummy if choiceT3==0

gen WINT2_ALL=0
replace WINT2_ALL=1 if WINT2==1 & treatment ==1
replace WINT2_ALL=1 if WINT2_GQ==1 & treatment ==2
replace WINT2_ALL=1 if WINT2_GQ==1 & GQT2==1 & treatment ==3
replace WINT2_ALL=1 if WINT2_CL==1 & GQT2==0 & treatment ==3


gen WINT3_ALL=0
replace WINT3_ALL=1 if WINT3==1 & treatment ==1
replace WINT3_ALL=1 if WINT3_GQ==1 & treatment ==2
replace WINT3_ALL=1 if WINT3_GQ==1 & GQT3==1 & treatment ==3
replace WINT3_ALL=1 if WINT3_CL==1 & GQT3==0 & treatment ==3

//...............GENERATING NEW VARIABLES.....................//

gen type=.
replace type=1 if gender==0 & Luck ==0
replace type=2 if gender==0 & Luck ==1
replace type=3 if gender==1 & Luck ==0
replace type=4 if gender==1 & Luck ==1

*favored dummy
gen favoredT2=0
replace favoredT2=1 if gender==0 & treatment==1
replace favoredT2 = 1 if treatment ==2 & gender==0
replace favoredT2 = 1 if treatment ==3 & type!=4
replace favoredT2 = 0 if treatment ==3 & type==4

*replace favoredT2 = 1 if treatment ==2 & gender==0
*replace favoredT2 = 1 if treatment ==3 & GQT2==1 & gender==0
*replace favoredT2 = 1 if treatment ==3 & GQT2==0 & Luck==0

gen favoredT3=0
replace favoredT3=1 if gender==0 & treatment==1
replace favoredT3 = 1 if treatment ==2 & gender==0
replace favoredT3 = 1 if treatment ==3 & type!=4
replace favoredT3 = 0 if treatment ==3 & type==4

*gen favoredT3=0
*replace favoredT3 = 1 if treatment ==2 & gender==0
*replace favoredT3 = 1 if treatment ==3 & GQT3==1 & gender==0
*replace favoredT3 = 1 if treatment ==3 & GQT3==0 & Luck==0

gen favoredT4=0
replace favoredT4=1 if gender==0 & treatment==1
replace favoredT4 = 1 if treatment ==2 & gender==0
replace favoredT4 = 1 if treatment ==3 & type!=4
replace favoredT4 = 1 if treatment ==3 & type==4

*gen favoredT4=0
*replace favoredT4 = 1 if treatment ==2 & gender==0
*replace favoredT4 = 1 if treatment ==3 & GQT4==1 & gender==0
*replace favoredT4 = 1 if treatment ==3 & GQT4==0 & Luck==0

gen choiceT3_p = choiceT3*100
gen choiceT4_p = choiceT4*100

gen female = 1 if gender==0
replace female=0 if gender==1

gen ctr=1 if treatment==1
replace ctr=0 if ctr!=1
gen genderq=1 if treatment==2
replace genderq=0 if genderq!=1
gen mixq=1 if treatment==3
replace mixq =0 if mixq!=1

gen genderq_favT3= genderq*female
gen mixq_favT3= mixq*favoredT3
gen mixq_female = mixq*female

*gen ctr_risk = ctr*risk
*gen genderq_risk = genderq*risk
*gen mixq_risk = mixq * risk
gen risk_favT3= favoredT3*risk

*deviation of belief rank from true rank
gen dev_T3 = beliefT3 - rankT3dummy
gen dev_T2 = beliefT2 - rankT2
gen dev_T1= beliefT1 - rankT1
gen undercon_T3= beliefT3 - rankT3dummy if rankT3<6
gen overcon_T3=  rankT3dummy - beliefT3 if rankT3>1

*fairness
gen fairness= fairness_CTR if treatment==1
replace fairness= fairness_gender if treatment==2
replace fairness= fairness_mix if treatment==3

*Dummy for three types of group compositions: 2 male/2female/1male 1 female leaders 
gen L1_m1 = 1 if Number==Leader1 & gender==1
gen L2_m1 = 1 if Number==Leader2 & gender==1
gen L1_f1 = 1 if Number==Leader1 & gender==0
gen L2_f1 = 1 if Number==Leader2 & gender==0

bysort uGroup: egen L1_m2 = max (L1_m1)
bysort uGroup: egen L2_m2 = max (L2_m1)
bysort uGroup: egen L1_f2 = max (L1_f1)
bysort uGroup: egen L2_f2 = max (L2_f1)

gen Leader_bothmale = 1 if L1_m2+L2_m2==2
gen Leader_bothfemale = 1 if L1_f2+L2_f2==2
gen Leader_1male1female=1 if Leader_bothmale!=1 & Leader_bothfemale!=1

drop L1_* L2_*

gen Num_femalecol = 3 if Leader_bothmale ==1
replace Num_femalecol= 1 if Leader_bothfemale==1
replace Num_femalecol = 2 if Leader_1male1female==1
gen Num_malecol = 3 if Leader_bothfemale ==1
replace Num_malecol= 1 if Leader_bothmale==1
replace Num_malecol = 2 if Leader_1male1female==1

gen Num_femaleleader= 0 if Leader_bothmale ==1
replace Num_femaleleader= 2 if Leader_bothfemale ==1
replace Num_femaleleader= 1 if Leader_1male1female==1

gen Num_maleleader= 0 if Leader_bothfemale ==1
replace Num_maleleader= 2 if Leader_bothmale ==1
replace Num_maleleader= 1 if Leader_1male1female==1

*Calculate the optimal team selection and performance
**chosen_l vs. optimal_l
****find highest performing leader
egen best_l1 = max(correctT5) if isLeader==1, by(uGroup)
****put this value to all observations in each group
egen best_l_performance = max(best_l1),by(uGroup)
****Find the number of the best leader
gen best_l2 = Number if correctT5-best_l_performance==0
egen best_leader = max(best_l2), by(uGroup)
****Find performance of each leader
gen l1_p = correctT5 if Number==Leader1
egen L1_performance = max(l1_p), by(uGroup)
gen l2_p = correctT5 if Number==Leader2
egen L2_performance = max(l2_p), by(uGroup)
gen chosen_l_performance = L1_performance if chosen_l == Leader1
replace chosen_l_performance = L2_performance if chosen_l == Leader2
****Find the difference in performance between chosen leader and best leader
gen diff_leader_performance = best_l_performance - chosen_l_performance

****Find gender of each leader
gen L1_g= gender if Leader1==Number
gen L2_g= gender if Leader2==Number
egen L1_gender= max(L1_g), by(uGroup)
egen L2_gender= max(L2_g), by(uGroup)

quietly drop best_l2 best_l1 L1_performance L1_g L2_g

**chosen_c vs. optimal_c
egen rank_c = rank(-correctT5) if isLeader==0, by(uGroup) track 
*count number of colleagues with rank =1 and rank=2
egen num_rankc1= total(rank_c==1), by(uGroup)
egen num_rankc2= total(rank_c==2), by(uGroup)
tab num_rankc1
tab num_rankc2
*find number of colleagues with rank 1 and rank 2
gen c1_dummy= Number if rank_c==1 
egen c1_1 = max(c1_dummy) , by(uGroup)
egen c1_2= min(c1_dummy)if num_rankc1!=1, by(uGroup)
egen c1_3= median(c1_dummy)if num_rankc1==3, by(uGroup) 

gen c2_dummy = Number if rank_c==2
egen c2_1= max(c2_dummy), by(uGroup)
egen c2_2= min(c2_dummy) if num_rankc2!=1, by(uGroup)
egen c2_3= median(c2_dummy) if num_rankc2==3,by(uGroup)

*find performance of each
gen c1_1p= correctT5 if Number== c1_1
egen c1_1performance = max (c1_1p), by(uGroup)
gen c1_2p= correctT5 if Number== c1_2
egen c1_2performance = max (c1_2p), by(uGroup)
gen c1_3p= correctT5 if Number== c1_3
egen c1_3performance = max (c1_3p), by(uGroup)

gen c2_1p= correctT5 if Number== c2_1
egen c2_1performance = max (c2_1p), by(uGroup)
gen c2_2p= correctT5 if Number== c2_2
egen c2_2performance = max (c2_2p), by(uGroup)
gen c2_3p= correctT5 if Number== c2_3
egen c2_3performance = max (c2_3p), by(uGroup)

quietly drop l1_p l2_p c1_dummy c2_dummy c1_1p c1_2p c1_3p c2_1p c2_2p c2_3p

*find optimal group performance
gen optimal_group_performance=.
replace optimal_group_performance= correctT5+ c1_1performance + c2_1performance if isLeader==1 & num_rankc1==1
replace optimal_group_performance= correctT5+ 2*c1_1performance if isLeader==1 & num_rankc1!=1

replace optimal_group_performance= correctT5+ best_l_performance + c2_1performance if isLeader==0 & rank_c==1 & num_rankc1==1
replace optimal_group_performance= 2*correctT5+ best_l_performance  if isLeader==0 & rank_c==1 & num_rankc1!=1
replace optimal_group_performance= correctT5+ best_l_performance+ c1_1performance  if isLeader==0 & rank_c!=1 

*find difference between optimal group performance and real group performance
gen diff_group_performance= optimal_group_performance - Teamcorrect
gen diff_group_performanceratio = 1-(Teamcorrect/optimal_group_performance)

gen diff_leader_performanceratio = 1 - (chosen_l_performance/best_l_performance)

gen diff_colleague_performance = diff_group_performance - diff_leader_performance
replace diff_colleague_performance= diff_group_performance if isLeader==1

gen diff_colleague_performanceratio = 1- (Teamcorrect - chosen_l_performance)/(optimal_group_performance - best_l_performance)

quietly drop B subjects Participate N Q show_message abs_time_last_answer abs_time_last_correct_answer abs_time_start_message 
quietly drop x max_work_time max_work_time_seconds dummy new_sheet p radio_left radio_right radio_min radio_max radio_default 
quietly drop num_rows_CE num_rows_B TimeWeiterS1InfoAboutOwnColorOK TimeWeiterOnScreenInstructionOK TimeOKS2ControlQuestionOK 
quietly drop TimeOKS3ControlQuestion1OK TimeOKS5ControlQuestionOK TimeStuecklohnS3OptionPROrTROK TimeWettbewerbS3OptionPROrTROK 
quietly drop N1 N2 N3 N4 N5 SUM Sum Last TimeBestaetigenS0MathTaskTrialOK TimeBestaetigenS123MathTaskOK TimeStuecklohnS4OptionPROrTROK 
quietly drop TimeWettbewerbS4OptionPROrTROK TimeWeiterS5PartnerSelectionOK show_button_guess same_sheet last_was_false i row1 row2 row3 
quietly drop row4 row5 row6 row7 row8 row9 row10 lzeros1 lzeros2 lzeros3 lzeros4 lzeros5 lzeros6 lzeros7 lzeros8 lzeros9 lzeros10 r ous 
quietly drop TimeOKS5TaskTeamWorkOK TimeOKS5TeamworkOutcomeOK TimeWeiterS5BeliefInstructionOK TimeWeiterS5BeliefOK TimeWeiterFairness1CTROK 
quietly drop TimeWeiterFairness2CTROK TimeWeiterS5QuestionnaireAboutMa TimeWeiterIntroQuestionnairesOK TimeWeiterGeneralRiskQuestionOK 
quietly drop TimeWeiterInstructionRiskChoiceL p_a p_c y_a y_b y_c d_y d_p TimeWeiterRiskChoiceListOK TimeWeiterCognitiveReflectionTes 
quietly drop TimeWeiterSocialPreference1OK TimeWeiterSocialPreference2OK TimeWeiterPreference2OK TimeWeiterPreference3OK TimeWeiterSociodemographicsOK 
quietly drop TimeWeiterShowFinalPayoffOK TimeWeiterFairness1GENDEROK TimeWeiterFairness2GENDEROK TimeOKS2ControlQuestion2OK TimeWeiterFairness1MIXOK 
quietly drop TimeWeiterFairness2MIXOK TimeWeiterS4BeliefInstructionOK TimeWeiterS4BeliefOK JW IW

quietly drop columns thequestion_number zeros participant ChoiceB1 ChoiceB2 ChoiceB3 ChoiceB4 ChoiceB5 ChoiceB6 ChoiceB7 ChoiceB8 ChoiceB9 ChoiceB10 ChoiceB11 
quietly drop control_q1 time_control_q1 control_s3_1_start control_s3q1 control_s5q1 s3_choice_start s3_choice_time s3_choice_end 
quietly drop nR1 nR2 Srank Rank m1 m2 sameR higherR rW1 rW2 valid_pselect LeaveStage rand_draw_stage Draw_stage starttime_riskchoicelist time_riskchoicelist
quietly drop  CRT_1 CRT_2 CRT_3 IG correct_ctrl control_q2 sameR_mh Rank_mh nR1_w Srank_w Rank_w nW_w sameR_w rW1_w sameR_m higherR_m 
quietly drop correct_ctrl2 control_q2_2 random_m random_mg sameR_lh Rank_lh nR1_u Srank_u Rank_u nW_u sameR_u rW1_u sameR_l higherR_l rW1_l  rankT3dummy
quietly drop rankT2_w rankT3_w rankT4_w rankT2_u rankT3_u rankT4_u

la var Num_voteleader "Total"
la var Num_voteleader_byMen "Votes by males"
la var Num_voteleader_byWomen "Votes by females"

la var Num_votecol "Total"
la var Num_votecol_byMen "Votes by males"
la var Num_votecol_byWomen "Votes by females"

cd ..
save "AAidentity.dta", replace





